package com.ejoysoft.wlanyz.business;


import java.io.File;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.ejoysoft.auth.MD5;
import com.ejoysoft.common.Constants;
import com.ejoysoft.common.DbConnect;
import com.ejoysoft.common.Format;
import com.ejoysoft.common.Globa;
import com.ejoysoft.common.UID;
import com.sun.org.apache.bcel.internal.generic.NEW;



public class Ap {
	private Globa globa;
    private DbConnect db;
    private DbConnect dd = new DbConnect();

    public Ap() {
    }

    public Ap(Globa globa) {
        this.globa = globa;
        db = globa.db;
        dd = globa.db;
    }

    public Ap(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        dd = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName = "RM_DEVICE";

    
  //增加
    public boolean add(String strMac_Addr) {
        String strSql = "";
        device_Id = UID.getID();
        
        try {
        	//把用户信息写入数据库
            strSql = "INSERT INTO " + strTableName + "  (device_Id, device_Name, device_Alias, strProvince, strCity, " +
            		"strTown, strCounty, strHotSpot, strAc_Name, strSwitch_Name, strOnu_Name, strCoverage_Type, " +
            		"strMac_Addr, strVocerage_Area ,strIp_Addr, strIp_Mask, strSsId, strInter_Standard, strDevice_Type, " +
            		"strDevice_Position, isEquence_Number, device_Model, device_Vendor, read_Community, write_Community, " +
            		"strCreator, dtCreatDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'))";
            db.prepareStatement(strSql);
            db.setString(1, device_Id);
            db.setString(2, device_Name);
            db.setString(3, device_Alias);  
            db.setString(4, strProvince);
            db.setString(5, strCity);
            db.setString(6, strTown);
            db.setString(7, strCounty);
            db.setString(8, strHotSpot);
            db.setString(9, strAc_Name);
            db.setString(10, strSwitch_Name);
            db.setString(11, strOnu_Name);
            db.setString(12, strCoverage_Type);               
            db.setString(13, strMac_Addr);
            db.setString(14, strVocerage_Area);
            db.setString(15, strIp_Addr);
            db.setString(16, strIp_Mask);
            db.setString(17, strSsId);
            db.setString(18, strInter_Standard);
            db.setString(19, strDevice_Type);
            db.setString(20, strDevice_Position);
            db.setString(21, isEquence_Number);
            db.setString(22, device_Model);
            db.setString(23, device_Vendor);
            db.setString(24, read_Community);
            db.setString(25, write_Community);
            db.setString(26, strCreator);
            db.setString(27, Format.getDateTime());
            if (db.executeUpdate() > 0) {
                Globa.logger0("增加用户信息", globa.loginName, globa.loginIp, strSql, "用户管理", globa.userSession.getStrDepart());
                System.out.println("!!!!!!!!!!!!!!");
                return true;
            } else
                return false;
        } catch (Exception e) {
            System.out.println("增加用户信息时出错！！");
            e.printStackTrace();
            return false;
        }
    }
   //删除
    public boolean delete(String where,String device_Id) {
        try {
        	
            String sql = "DELETE FROM " + strTableName + "  ".concat(where);
            System.out.println(sql);
            db.executeUpdate(sql);         
            return true;
        } catch (Exception ee) {
            ee.printStackTrace();
            return false;
        }
    }
    //修改
    public boolean update(String device_Id) {
        try {
            String strSql = "UPDATE  " + strTableName + "  SET " +
            		        " device_Name=?, device_Alias=?, strProvince=? ,strCity=?, strTown=?, strCounty=?," +
            				" strHotSpot=?, strAc_Name=?, strSwitch_Name=?, strOnu_Name=?, strCoverage_Type=?, " +
            				" strMac_Addr=?, strVocerage_Area=?, strIp_Addr=?, strIp_Mask=?, strSsId=?, strInter_Standard=?," +
            				" strDevice_Type=?, strDevice_Position=?, isEquence_Number=?, device_Model=?, device_Vendor=?, " +
            				" read_Community=?, write_Community=?, strCreator=? , dtCreatDate= to_date(?,'yyyy-MM-dd hh24:mi:ss') WHERE device_Id=? ";
            db.prepareStatement(strSql);
            db.setString(1, device_Name);
            db.setString(2, device_Alias);   //strPWD
            db.setString(3, strProvince);
            db.setString(4, strCity);
            db.setString(5, strTown);
            db.setString(6, strCounty);
            db.setString(7, strHotSpot);
            db.setString(8, strAc_Name);
            db.setString(9, strSwitch_Name);
            db.setString(10, strOnu_Name);
            db.setString(11, strCoverage_Type);                 //strUnitCode                  new Unit(globa, false).retFieldValue("strUnitCode", strUnitId)
            db.setString(12, strMac_Addr);
            db.setString(13, strVocerage_Area);
            db.setString(14, strIp_Addr);
            db.setString(15, strIp_Mask);
            db.setString(16, strSsId);
            db.setString(17, strInter_Standard);
            db.setString(18, strDevice_Type);
            db.setString(19, strDevice_Position);
            db.setString(20, isEquence_Number);
            db.setString(21, device_Model);
            db.setString(22, device_Vendor);
            db.setString(23, read_Community);
            db.setString(24, write_Community);
            db.setString(25, strCreator);
            db.setString(26, Format.getDateTime());
            db.setString(27, device_Id);
            db.executeUpdate();
            Globa.logger0("修改用户信息", globa.loginName, globa.loginIp, strSql, "用户管理", globa.userSession.getStrDepart());
            return true;
        } catch (Exception e) {
            System.out.println("修改用户信息时出错：" + e);
            e.printStackTrace();
            return false;
        }
    }

//返回单条记录
    public Ap show(String where) {
        try {
            String strSql = "select * from  " + strTableName + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }

  //查询符合条件的记录总数
    public int getCount(String where) {
        int count = 0;
        try {
            String sql = "SELECT count(device_Id) FROM " + strTableName + "  ";
            if (where.length() > 0) {
                where = where.toLowerCase();
                if (where.indexOf("order") > 0)
                    where = where.substring(0, where.lastIndexOf("order"));
                sql = String.valueOf(sql) + String.valueOf(where);
            }
            ResultSet rs = db.executeQuery(sql);
            if (rs.next())
                count = rs.getInt(1);
            rs.close();
            return count;
        } catch (Exception ee) {
            ee.printStackTrace();
            return count;
        }
    }



    //记录集转化为对象
    public Ap load(ResultSet rs, boolean isView) {
        Ap theBean = new Ap();
        try {
        	
        	theBean.setDevice_Id(rs.getString(1));
            theBean.setDevice_Name(rs.getString(2));
            theBean.setDevice_Alias(rs.getString(3));
            theBean.setStrProvince(rs.getString(4));
            theBean.setStrCity(rs.getString(5));
            theBean.setStrTown(rs.getString(6));
            theBean.setStrCounty(rs.getString(7));
            String strHotSpotId = rs.getString(8);
            theBean.setStrHotSpot(strHotSpotId);
            HotSpot obj = new HotSpot(globa);
            HotSpot obj1 = obj.show(" where strid='"+strHotSpotId+"'");
            theBean.setStrHotSpotName(obj1==null?"":obj1.getStrHotPotName());
            theBean.setStrClassOneHP(obj1==null?"":obj1.getStrClassOneHP());
            theBean.setStrAc_Name(rs.getString(9));
            theBean.setStrSwitch_Name(rs.getString(10));
            theBean.setStrOnu_Name(rs.getString(11));
            theBean.setStrCoverage_Type(rs.getString(12));
            theBean.setStrMac_Addr(rs.getString(13));
            theBean.setStrVocerage_Area(rs.getString(14));
            theBean.setStrIp_Addr(rs.getString(15));
            theBean.setStrIp_Mask(rs.getString(16));
            theBean.setStrSsId(rs.getString(17));
            theBean.setStrInter_Standard(rs.getString(18));
            theBean.setStrDevice_Type(rs.getString(19));
            theBean.setStrDevice_Position(rs.getString(20));
            theBean.setIsEquence_Number(rs.getString(21));
            theBean.setDevice_Model(rs.getString(22));
            theBean.setDevice_Vendor(rs.getString(23));
            theBean.setRead_Community(rs.getString(24));
            theBean.setWrite_Community(rs.getString(25));
            theBean.setStrCreator(rs.getString(26));
            theBean.setDtCreatDate(rs.getString(27));
            
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }

  //列表记录集
    public Vector<Ap> list(String where, int startRow, int rowCount) {
        Vector<Ap> beans = new Vector<Ap>();
        try {
            String sql = "SELECT *  FROM  " + strTableName + " ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
            ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                	Ap theBean = new Ap();
                    theBean = load(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        return beans;
        
    }
    
    //列表记录集
    public Vector<String> listDeviceId(String where) {
        Vector<String>  beans = new Vector<String>();
        try {
          String sql = "SELECT device_id from  "+ strTableName+ " ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            ResultSet rs = db.executeQuery(sql);
            if (rs != null && rs.next()) {
               do { 
                    beans.add(rs.getString("device_Id"));
                } while (rs.next());
            }
            rs.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        db.closeCon();
        return beans;
        
    }
    public Map<String, ArrayList<String>>  getAllclassTwoByName(){
    	Map<String, ArrayList<String>> map = new HashMap<String, ArrayList<String>>();
    	String sql = "select distinct(strHotSpotName) from " + strTableName ;
    	try {
    		ResultSet rs = db.executeQuery(sql);
        	if (rs != null && rs.next()) {
                do { 
                	 ArrayList<String> list = new ArrayList<String>();
                     String hotspot = rs.getString(1);
                     String sql1 = "select distinct(device_Name) from " + strTableName + " where strHotSpotName like " + "'%" + hotspot +"%' ";
                     ResultSet rs1 = db.executeQuery(sql1);
                     if(rs1 !=null && rs1.next()){
                    	 do{
                    		 String apName = rs1.getString(1);
                        	 list.add(apName);
                    	 }while(rs1.next());
                     }
                     rs1.close();
                     //for(int i=0;i<list.size();i++)System.err.println("----------------------i:"+ hotspot+"::"+list.get(i));
                     map.put(hotspot, list);
                 } while (rs.next());
             }
             rs.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		Iterator it = map.keySet().iterator();
		  while(it.hasNext()){
		   String key = (String) it.next();
		   ArrayList<String> value = map.get(key);
		  } 
		  
    	return map;
    }
    public Ap show1(String where) {
        try {
            String strSql = "select device_Name,strHotSpot  from  " + strTableName + "  ".concat(where);
            
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load1(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }
    public Ap load1(ResultSet rs, boolean isView) {
        Ap theBean = new Ap();
        try {
            theBean.setDevice_Name(rs.getString("device_Name"));
            theBean.setStrHotSpot(rs.getString("strHotSpot"));
         } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }
    public Ap show2(String where) {
        try {
            String strSql = "select device_Name,strMac_Addr  from  " + strTableName + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
               return load2(rs, true);
            else
                return null;
        } catch (Exception ee) {
        	ee.printStackTrace();
            return null;
        }
    }
    public Ap load2(ResultSet rs, boolean isView) {
        Ap theBean = new Ap();
        try {
            theBean.setDevice_Name(rs.getString("device_Name"));
            theBean.setStrMac_Addr(rs.getString("strMac_Addr"));
         } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }
    
  //********************导入**************************************
    public void insertinto(String s1,String s2,String s3,String s4,String s5,String s6,String s7,String s8,String s9,String s10,String s11,String s12,String s13,String s14,String s15,String s16,String s17,String s18,String s19,String s20){
    	String strSql = "";
		try {
			String device_Id = UID.getID();
	        strSql = "INSERT INTO RM_DEVICE (device_Id, device_Name, device_Alias, strMac_Addr, strHotSpot, strAc_Name," +
	        		" strSwitch_Name, strOnu_Name, strCoverage_Type, strVocerage_Area, strIp_Addr, strIp_Mask, strSsId," +
	        		" strInter_Standard, strDevice_Type, strDevice_Position, isEquence_Number, device_Model, device_Vendor," +
	        		" strCreator, dtCreatDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'))";
	        //strSql = "INSERT INTO RM_DEVICE (device_Id, device_Name, device_Alias, strMac_Addr, strHotSpot)VALUES (?,?,?,?,?)";
	        //System.out.println("huoqushijian:"+Format.getDateTime());
	        String s21 = Format.getDateTime();
	        dd.prepareStatement(strSql);
	        dd.setString(1, device_Id);
	        dd.setString(2, s1);
	        dd.setString(3, s2);
	        dd.setString(4, s3);
	        dd.setString(5, s4);
	        dd.setString(6, s5);
	        dd.setString(7, s6);
	        dd.setString(8, s7);
	        dd.setString(9, s8);
	        dd.setString(10, s9);
	        dd.setString(11, s10);
	        dd.setString(12, s11);
	        dd.setString(13, s12);
	        dd.setString(14, s13);
	        dd.setString(15, s14);
	        dd.setString(16, s15);
	        dd.setString(17, s16);
	        dd.setString(18, s17);
	        dd.setString(19, s18);
	        dd.setString(20, s19);
	        dd.setString(21, s21);
	        System.out.println("xxxxxxxxxxxxxxxxxxxxxxs4:"+s4);
	        if (dd.executeUpdate() > 0) {
	        	//Globa.logger0("导入excel表格成功", globa.loginName, globa.loginIp, strSql, "资源管理", globa.userSession.getStrDepart());
	        	System.out.println("导入成功");
	        } 
		} catch (Exception e) {
			//dd.rollback();
	        e.printStackTrace();
	        //dd.closeCon();
		}
		dd.closeCon();
    }
    public void method(String url){
    	try
	    {
	    List<String> list = new ArrayList<String>();
	    Workbook workBook=Workbook.getWorkbook(new File(url));
	    //获得第一个工作表对象
	    Sheet sheet=workBook.getSheet(0);
	    
	    //得到第一列第一行的单元格
	    Cell cell=null;
	
	    
	    for (int i = 1; i < sheet.getRows(); i++){
	        List<String> list2 = new ArrayList<String>();
	        for (int j = 0; j < sheet.getColumns(); j++) {
	        	cell = sheet.getCell(j, i);
	        	list2.add(cell.getContents().toString());//将每行的字符串用一个String类型的集合保存。
	        }
	        //System.out.println("输出list的大小："+list2.size());
	        //System.out.println("输出一行："+list2.get(18)+","+list2.get(19)+","+list2.get(17));
	        insertinto(list2.get(0),list2.get(1),list2.get(2),list2.get(3),list2.get(4),list2.get(5),list2.get(6),list2.get(7),list2.get(8),list2.get(9),list2.get(10),list2.get(11),list2.get(12),list2.get(13),list2.get(14),list2.get(15),list2.get(16),list2.get(17),list2.get(18),list2.get(19));
	    }
	    	workBook.close();//记得关闭
	      
	      //迭代集合查看每行的数据
		    
		    
	    }catch(Exception e)
	    {
	    System.out.println(e);
	    }
    }

    private String device_Id;//	设备id
    private String device_Name;//设备名
    private String device_Alias;//设备别名
    private String strProvince;//省份
    private String strCity;//地市
    private String strTown;//县区
    private String strCounty;//国家
    private String strHotSpot;//热点
    private String strHotSpotName;//二级热点

	private String strAc_Name;//AC名
    private String strSwitch_Name;//转换器名称
    private String strOnu_Name;//光纤名
    private String strCoverage_Type;//覆盖类型
    private String strMac_Addr;//MAC地址
    private String strVocerage_Area;//覆盖区域
    private String strIp_Addr;//IP地址
    private String strIp_Mask;//IP网关
    private String strSsId;//SSID
    private String strInter_Standard;//标准
    private String strDevice_Type;//设备种类
    private String strDevice_Position;//设备位置
    private String isEquence_Number;//识别名
    private String device_Model;//设备模型
    private String device_Vendor;//设备供应商
    private String read_Community;//
    private String write_Community;//
    private String strCreator;//操作人
    private String dtCreatDate;//操作时间

    private String strClassOneHP;//一级热点

    

    public String getStrClassOneHP() {
		return strClassOneHP;
	}

	public void setStrClassOneHP(String strClassOneHP) {
		this.strClassOneHP = strClassOneHP;
	}

	public String getDtCreatDate() {
		return dtCreatDate;
	}

	public void setDtCreatDate(String dtCreatDate) {
		this.dtCreatDate = dtCreatDate;
	}

	public String getStrHotSpotName() {
		return strHotSpotName;
	}

	public void setStrHotSpotName(String strHotSpotName) {
		this.strHotSpotName = strHotSpotName;
	}


	public String getDevice_Id() {
		return device_Id;
	}

	public void setDevice_Id(String deviceId) {
		device_Id = deviceId;
	}

	public String getDevice_Name() {
		return device_Name;
	}

	public void setDevice_Name(String deviceName) {
		device_Name = deviceName;
	}



	public String getDevice_Alias() {
		return device_Alias;
	}

	public void setDevice_Alias(String deviceAlias) {
		device_Alias = deviceAlias;
	}

	public String getStrProvince() {
		return strProvince;
	}

	public void setStrProvince(String strProvince) {
		this.strProvince = strProvince;
	}

	public String getStrCity() {
		return strCity;
	}

	public void setStrCity(String strCity) {
		this.strCity = strCity;
	}

	public String getStrTown() {
		return strTown;
	}

	public void setStrTown(String strTown) {
		this.strTown = strTown;
	}

	public String getStrCounty() {
		return strCounty;
	}

	public void setStrCounty(String strCounty) {
		this.strCounty = strCounty;
	}

	public String getStrHotSpot() {
		return strHotSpot;
	}

	public void setStrHotSpot(String strHotSpot) {
		this.strHotSpot = strHotSpot;
	}

	public String getStrAc_Name() {
		return strAc_Name;
	}

	public void setStrAc_Name(String strAcName) {
		strAc_Name = strAcName;
	}

	public String getStrSwitch_Name() {
		return strSwitch_Name;
	}

	public void setStrSwitch_Name(String strSwitchName) {
		strSwitch_Name = strSwitchName;
	}

	public String getStrOnu_Name() {
		return strOnu_Name;
	}

	public void setStrOnu_Name(String strOnuName) {
		strOnu_Name = strOnuName;
	}

	public String getStrCoverage_Type() {
		return strCoverage_Type;
	}

	public void setStrCoverage_Type(String strCoverageType) {
		strCoverage_Type = strCoverageType;
	}

	public String getStrMac_Addr() {
		return strMac_Addr;
	}

	public void setStrMac_Addr(String strMacAddr) {
		strMac_Addr = strMacAddr;
	}




	public String getStrVocerage_Area() {
		return strVocerage_Area;
	}

	public void setStrVocerage_Area(String strVocerageArea) {
		strVocerage_Area = strVocerageArea;
	}

	public String getStrIp_Addr() {
		return strIp_Addr;
	}

	public void setStrIp_Addr(String strIpAddr) {
		strIp_Addr = strIpAddr;
	}

	public String getStrIp_Mask() {
		return strIp_Mask;
	}

	public void setStrIp_Mask(String strIpMask) {
		strIp_Mask = strIpMask;
	}

	public String getStrSsId() {
		return strSsId;
	}

	public void setStrSsId(String strSsId) {
		this.strSsId = strSsId;
	}

	public String getStrInter_Standard() {
		return strInter_Standard;
	}

	public void setStrInter_Standard(String strInterStandard) {
		strInter_Standard = strInterStandard;
	}

	public String getStrDevice_Type() {
		return strDevice_Type;
	}

	public void setStrDevice_Type(String strDeviceType) {
		strDevice_Type = strDeviceType;
	}

	public String getStrDevice_Position() {
		return strDevice_Position;
	}

	public void setStrDevice_Position(String strDevicePosition) {
		strDevice_Position = strDevicePosition;
	}

	public String getIsEquence_Number() {
		return isEquence_Number;
	}

	public void setIsEquence_Number(String isEquenceNumber) {
		isEquence_Number = isEquenceNumber;
	}

	public String getDevice_Model() {
		return device_Model;
	}

	public void setDevice_Model(String deviceModel) {
		device_Model = deviceModel;
	}

	public String getDevice_Vendor() {
		return device_Vendor;
	}

	public void setDevice_Vendor(String deviceVendor) {
		device_Vendor = deviceVendor;
	}

	public String getRead_Community() {
		return read_Community;
	}

	public void setRead_Community(String readCommunity) {
		read_Community = readCommunity;
	}

	public String getWrite_Community() {
		return write_Community;
	}

	public void setWrite_Community(String writeCommunity) {
		write_Community = writeCommunity;
	}

	public String getStrCreator() {
		return strCreator;
	}

	public void setStrCreator(String strCreator) {
		this.strCreator = strCreator;
	}

    
    
}